import sqlite3
import sys

def create_tables(dbfile):
  db = sqlite3.connect(dbfile)
  c = db.cursor()
  c.execute('''
    CREATE TABLE IF NOT EXISTS Course (
      code VARCHAR,
      title VARCHAR,
      PRIMARY KEY(code)
    )
  ''')

  c.execute(''' create table if not exists Section
    (sec VARCHAR,
     crn VARCHAR,
     code VARCHAR,
     semester VARCHAR,
     maxsize INTEGER,
     actsize INTEGER,
     duration INTEGER,
     recurrence VARCHAR,
     primary key (sec, code, semester)
     )''')

  c.execute('''
    create table if not exists Require
    (id INTEGER PRIMARY KEY,
     sec VARCHAR,
     code VARCHAR,
     semester VARCHAR,
     feature VARCHAR)''')

  c.execute('''
    create table if not exists Schedule
    (sec VARCHAR, 
     code VARCHAR, 
     semester VARCHAR, 
     schd VARCHAR,
     campus VARCHAR,
     location VARCHAR, 
     day VARCHAR,
     btime VARCHAR,
     etime VARCHAR,
     startweek VARCHAR,
     primary key (sec, code, semester, location, day, btime, etime))''')

  c.execute('''create table if not exists Room
    (campus VARCHAR,
     location VARCHAR,
     capacity INTEGER,
     remark VARCHAR,
     PRIMARY KEY(campus,location))''')

  c.execute('''create table if not exists StartTime
    (btime VARCHAR,
     day VARCHAR,
     PRIMARY KEY(btime,day))''')

  c.execute('''create table if not exists Conflict
    (dept VARCHAR,
     program VARCHAR,
     progdesc VARCHAR,
     level VARCHAR,
     semester VARCHAR,
     code VARCHAR,
     coursedesc VARCHAR,
     category VARCHAR,
     catdesc VARCHAR,
     PRIMARY KEY(dept, program, level, semester, code))''')

  c.execute('''create table if not exists FreeRoom
    (semester VARCHAR,
     campus VARCHAR,
     location VARCHAR,
     capacity INTEGER,
     btime VARCHAR,
     btimemin INTEGER,
     day VARCHAR,
     duration INTEGER,
     PRIMARY KEY (semester, campus, location, btime, day, duration))''')

  print "Tables created."
  db.close()

def delete_tables(dbfile):
  db = sqlite3.connect(dbfile)
  c = db.cursor()
  c.execute("SELECT name FROM SQLITE_MASTER where type = ?", ('table',))
  for t in [x[0] for x in c.fetchall()]:
    print "DROP TABLE %s" % t
    c.execute("DROP TABLE %s" % t)
  c.close()
  db.close()

